﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Intelligent.OTC.Common.UnitOfWork;
using Intelligent.OTC.Domain.DataModel;
using Intelligent.OTC.Domain.Repositories;
using System.Configuration;
using System.IO;
using Intelligent.OTC.Domain;
using Intelligent.OTC.Common;
using System.Data.Entity.Validation;
using Intelligent.OTC.Common.Attr;
using Intelligent.OTC.Common.Utils;
using Intelligent.OTC.Common.Exceptions;
using System.Data.Entity;
using System.Web;
using Newtonsoft.Json;
using Intelligent.OTC.Business;
using System.Transactions;
using EntityFramework.BulkInsert.Extensions;

namespace Intelligent.OTC.Business
{
    public class SoaService : ISoaService
    {
        public OTCRepository CommonRep { get; set; }
        private string IsWF = ConfigurationManager.AppSettings["IsWF"].ToString();

        public int CurrentPeriod 
        {
            get {
                return CommonRep.GetDbSet<PeriodControl>()
                    .Where(o => o.Deal == AppContext.Current.User.Deal).Max(o => o.Id);
            }
        }

        public IEnumerable<SoaDto> GetSoaList(string invoiceState = "", string invoiceTrackState = "", string legalEntity = "", string invoiceNum = "", string soNum = "", string poNum = "", string invoiceMemo = "")
        {

            //PeriodControl Period = CommonRep.GetDbSet<PeriodControl>()
            //    .Where(o => o.Deal == AppContext.Current.User.Deal).OrderByDescending(o => o.Id).FirstOrDefault();
            //int latestPId = Period.Id;
            IQueryable<SoaDto> r = null;

            //var custalls = CommonRep.GetQueryable<CustomerTeam>().Where(o => o.Deal == AppContext.Current.User.Deal && o.InScope == "Y");
            //var conalls = CommonRep.GetQueryable<Contactor>().Where(o => o.Deal == AppContext.Current.User.Deal && o.IsDefaultFlg == "1");

            if (string.IsNullOrEmpty(invoiceState) && string.IsNullOrEmpty(invoiceTrackState) && string.IsNullOrEmpty(invoiceNum) && string.IsNullOrEmpty(soNum) && string.IsNullOrEmpty(poNum) && string.IsNullOrEmpty(invoiceMemo))
            {
                #region Without InvoiceState and InvoiceTrackState
                // base table: dunning
                r = (from final in
                         (from res in
                              (from soa in CommonRep.GetDbSet<CollectorAlert>()
                               // left join customer level
                               join cust in SpringFactory.GetObjectImpl<CustomerService>("CustomerService").GetCustomerLevel(CurrentPeriod) on new { soa.CustomerNum, soa.Deal } equals new { cust.CustomerNum, cust.Deal }
                                  into custs
                               from cust in custs.DefaultIfEmpty()
                               where soa.Status != "Cancelled" && soa.Status != "Finish" && (soa.AlertType == 1) && soa.ActionDate < AppContext.Current.User.Now && soa.PeriodId == CurrentPeriod
                               // left join aging
                               join age in CommonRep.GetQueryable<CustomerAging>().Where(o => o.Deal == AppContext.Current.User.Deal && o.RemoveFlg == "0") on new { cust.CustomerNum, cust.Deal } equals new { age.CustomerNum, age.Deal }
                                  into ages
                               from age in ages.DefaultIfEmpty()
                               // left join hold customer table
                               join ch in CommonRep.GetDbSet<HoldCustomer>() on cust.CustomerNum equals ch.CustomerNum
                                  into custHolds
                               select new
                               {
                                   Id = soa.Id,
                                   ActionDate = soa.ActionDate,
                                   Deal = cust.Deal,
                                   TaskId = soa.TaskId,
                                   ReferenceNo = soa.ReferenceNo,
                                   ProcessId = soa.ProcessId,
                                   SoaStatus = soa.Status,
                                   CauseObjectNumber = soa.CauseObjectNumber,
                                   BatchType = soa.BatchType,
                                   FailedReason = soa.FailedReason,
                                   PeriodId = soa.PeriodId,
                                   AlertType = soa.AlertType,
                                   CustomerNum = cust.CustomerNum,
                                   CustomerName = cust.CustomerName,
                                   BillGroupCode = cust.BillGroupCode,
                                   BillGroupName = cust.BillGroupName,
                                   // This is hold flag
                                   IsHoldFlg = custHolds.Any(ch => ch.IsHoldFlg == "1") ? "On Hold" : "Normal",
                                   // TODO: THis is used to shown hold flag type name in UI, We will handle this in UI.
                                   Operator = cust.Collector,
                                   // All contacts list, Another property "Contact" have logic to retrieve single line contact information.
                                   CreditLimit = age == null ? 0 : age.CreditLimit,
                                   TotalAmt = age == null ? 0 : age.TotalAmt,
                                   CurrentAmt = age == null ? 0 : age.CurrentAmt,
                                   FDueOver90Amt = age == null ? 0 : (age.Due120Amt + age.Due150Amt + age.Due180Amt + age.Due210Amt + age.Due240Amt + age.Due270Amt + age.Due300Amt + age.Due330Amt + age.Due360Amt + age.DueOver360Amt),
                                   PastDueAmt = age == null ? 0 : age.DueoverTotalAmt,
                                   Risk = cust.Risk,
                                   Value = cust.Value,
                                   Class = (string.IsNullOrEmpty(cust.ValueLevel) == true ? "LV" : cust.ValueLevel) +
                                           (string.IsNullOrEmpty(cust.RiskLevel) == true ? "LR" : cust.RiskLevel),
                               })
                          group res by new
                          {
                              res.Id,
                              res.ActionDate,
                              res.Deal,
                              res.TaskId,
                              res.ReferenceNo,
                              res.ProcessId,
                              res.SoaStatus,
                              res.CauseObjectNumber,
                              res.BatchType,
                              res.FailedReason,
                              res.PeriodId,
                              res.AlertType,
                              res.CustomerNum,
                              res.CustomerName,
                              res.BillGroupCode,
                              res.BillGroupName,
                              res.IsHoldFlg,
                              res.Operator,
                              res.Risk,
                              res.Value,
                              res.Class
                          }
                              into reses
                              select new
                              {
                                  Id = reses.Key.Id,
                                  ActionDate = reses.Key.ActionDate,
                                  Deal = reses.Key.Deal,
                                  TaskId = reses.Key.TaskId,
                                  ReferenceNo = reses.Key.ReferenceNo,
                                  ProcessId = reses.Key.ProcessId,
                                  SoaStatus = reses.Key.SoaStatus,
                                  CauseObjectNumber = reses.Key.CauseObjectNumber,
                                  BatchType = reses.Key.BatchType,
                                  FailedReason = reses.Key.FailedReason,
                                  PeriodId = reses.Key.PeriodId,
                                  AlertType = reses.Key.AlertType,
                                  CustomerNum = reses.Key.CustomerNum,
                                  CustomerName = reses.Key.CustomerName,
                                  BillGroupCode = reses.Key.BillGroupCode,
                                  BillGroupName = reses.Key.BillGroupName,
                                  Class = reses.Key.Class,
                                  Risk = reses.Key.Risk,
                                  CreditLimit = reses.Sum(age => age.CreditLimit),
                                  TotalAmt = reses.Sum(age => age.TotalAmt),
                                  CurrentAmt = reses.Sum(age => age.CurrentAmt),
                                  FDueOver90Amt = reses.Sum(age => age.FDueOver90Amt),
                                  PastDueAmt = reses.Sum(age => age.PastDueAmt),
                                  Operator = reses.Key.Operator,
                                  IsHoldFlg = reses.Key.IsHoldFlg
                              })
                     // left join contactor (contact in hierachy structure, not flat result of normal joins)
                     //join cont in
                     //    (from cust in custalls
                     //     from con in conalls
                     //     where cust.CustomerNum == con.CustomerNum || cust.BillGroupCode == con.GroupCode
                     //     select new
                     //     {
                     //         Id = con.Id,
                     //         CustomerNum = cust.CustomerNum,
                     //         EmailAddress = con.EmailAddress,
                     //         Deal = cust.Deal,
                     //         ToCc = con.ToCc,
                     //         Name = con.Name
                     //     })//CommonRep.GetDbSet<Contactor>() 
                     //   on new { final.CustomerNum, final.Deal } equals new { cont.CustomerNum, cont.Deal }
                     //   into conts
                     join aging in CommonRep.GetDbSet<CustomerAging>().Where(o => o.Deal == AppContext.Current.User.Deal && o.RemoveFlg == "0") on new { final.CustomerNum, final.Deal } equals new { aging.CustomerNum, aging.Deal }
                        into agings
                     where agings.Any(age => age.LegalEntity == legalEntity || string.IsNullOrEmpty(legalEntity))
                     select new SoaDto
                     {
                         Id = final.Id,
                         ActionDate = final.ActionDate,
                         Deal = final.Deal,
                         TaskId = final.TaskId,
                         ReferenceNo = final.ReferenceNo,
                         ProcessId = final.ProcessId,
                         SoaStatus = final.SoaStatus,
                         CauseObjectNumber = final.CauseObjectNumber,
                         BatchType = final.BatchType,
                         FailedReason = final.FailedReason,
                         PeriodId = final.PeriodId,
                         AlertType = final.AlertType,
                         CustomerNum = final.CustomerNum,
                         CustomerName = final.CustomerName,
                         BillGroupCode = string.IsNullOrEmpty(final.BillGroupCode) == true ? final.CustomerName : final.BillGroupCode,
                         BillGroupName = string.IsNullOrEmpty(final.BillGroupName) == true ? final.CustomerName : final.BillGroupName,
                         CreditLimit = final.CreditLimit,
                         TotalAmt = final.TotalAmt,
                         CurrentAmt = final.CurrentAmt,
                         FDueOver90Amt = final.FDueOver90Amt,
                         PastDueAmt = final.PastDueAmt,
                         //ExistCont = conts.Any() ? "Y" : "N",
                         //ContactList = from c in conts
                         //              select string.Concat(c.EmailAddress, "(", c.Name, ")"),
                         LegalEntityList = from l in agings
                                           select l.LegalEntity,
                         Class = final.Class,
                         Risk = final.Risk,
                         Operator = final.Operator,
                         IsHoldFlg = final.IsHoldFlg

                     });
                #endregion
            }
            else
            {
                #region With InvoiceState Or InvoiceTrackState
                // base table: dunning
                r = (from final in
                         (from res in
                              (from soa in CommonRep.GetDbSet<CollectorAlert>()
                               // left join customer level
                               join cust in SpringFactory.GetObjectImpl<CustomerService>("CustomerService").GetCustomerLevel(CurrentPeriod) on new { soa.CustomerNum, soa.Deal } equals new { cust.CustomerNum, cust.Deal }
                                  into custs
                               from cust in custs.DefaultIfEmpty()
                               where soa.Status != "Cancelled" && soa.Status != "Finish" && (soa.AlertType == 1) && soa.ActionDate < AppContext.Current.User.Now && soa.PeriodId == CurrentPeriod
                               // left join aging
                               join age in CommonRep.GetQueryable<CustomerAging>().Where(o => o.Deal == AppContext.Current.User.Deal && o.RemoveFlg == "0") on new { cust.CustomerNum, cust.Deal } equals new { age.CustomerNum, age.Deal }
                                  into ages
                               from age in ages.DefaultIfEmpty()
                               // left join hold customer table
                               join ch in CommonRep.GetDbSet<HoldCustomer>() on cust.CustomerNum equals ch.CustomerNum
                                  into custHolds
                               // EXISTS clause
                               join inv in CommonRep.GetDbSet<InvoiceAging>() on new { cust.CustomerNum, cust.Deal } equals new { inv.CustomerNum, inv.Deal }
                                into invs
                               where invs.Any(
                                            i => (i.States == invoiceState || string.IsNullOrEmpty(invoiceState))
                                         && (i.TrackStates == invoiceTrackState || string.IsNullOrEmpty(invoiceTrackState))
                                         && (i.InvoiceNum.IndexOf(invoiceNum) >= 0 || string.IsNullOrEmpty(invoiceNum))
                                         && (i.SoNum.IndexOf(soNum) >= 0 || string.IsNullOrEmpty(soNum))
                                         && (i.PoNum.IndexOf(poNum) >= 0 || string.IsNullOrEmpty(poNum))
                                         && (i.Comments.IndexOf(invoiceMemo) >= 0 || string.IsNullOrEmpty(invoiceMemo))
                                         )
                               select new
                               {
                                   Id = soa.Id,
                                   ActionDate = soa.ActionDate,
                                   Deal = cust.Deal,
                                   TaskId = soa.TaskId,
                                   ReferenceNo = soa.ReferenceNo,
                                   ProcessId = soa.ProcessId,
                                   SoaStatus = soa.Status,
                                   CauseObjectNumber = soa.CauseObjectNumber,
                                   BatchType = soa.BatchType,
                                   FailedReason = soa.FailedReason,
                                   PeriodId = soa.PeriodId,
                                   AlertType = soa.AlertType,
                                   CustomerNum = cust.CustomerNum,
                                   CustomerName = cust.CustomerName,
                                   BillGroupCode = cust.BillGroupCode,
                                   BillGroupName = cust.BillGroupName,
                                   // This is hold flag
                                   IsHoldFlg = custHolds.Any(ch => ch.IsHoldFlg == "1") ? "On Hold" : "Normal",
                                   // TODO: THis is used to shown hold flag type name in UI, We will handle this in UI.
                                   Operator = cust.Collector,
                                   // All contacts list, Another property "Contact" have logic to retrieve single line contact information.
                                   CreditLimit = age == null ? 0 : age.CreditLimit,
                                   TotalAmt = age == null ? 0 : age.TotalAmt,
                                   CurrentAmt = age == null ? 0 : age.CurrentAmt,
                                   FDueOver90Amt = age == null ? 0 : (age.Due120Amt + age.Due150Amt + age.Due180Amt + age.Due210Amt + age.Due240Amt + age.Due270Amt + age.Due300Amt + age.Due330Amt + age.Due360Amt + age.DueOver360Amt),
                                   PastDueAmt = age == null ? 0 : age.DueoverTotalAmt,
                                   Risk = cust.Risk,
                                   Value = cust.Value,
                                   Class = (string.IsNullOrEmpty(cust.ValueLevel) == true ? "LV" : cust.ValueLevel) +
                                           (string.IsNullOrEmpty(cust.RiskLevel) == true ? "LR" : cust.RiskLevel),
                               })
                          group res by new
                          {
                              res.Id,
                              res.ActionDate,
                              res.Deal,
                              res.TaskId,
                              res.ReferenceNo,
                              res.ProcessId,
                              res.SoaStatus,
                              res.CauseObjectNumber,
                              res.BatchType,
                              res.FailedReason,
                              res.PeriodId,
                              res.AlertType,
                              res.CustomerNum,
                              res.CustomerName,
                              res.BillGroupCode,
                              res.BillGroupName,
                              res.IsHoldFlg,
                              res.Operator,
                              res.Risk,
                              res.Value,
                              res.Class
                          }
                              into reses
                              select new
                              {
                                  Id = reses.Key.Id,
                                  ActionDate = reses.Key.ActionDate,
                                  Deal = reses.Key.Deal,
                                  TaskId = reses.Key.TaskId,
                                  ReferenceNo = reses.Key.ReferenceNo,
                                  ProcessId = reses.Key.ProcessId,
                                  SoaStatus = reses.Key.SoaStatus,
                                  CauseObjectNumber = reses.Key.CauseObjectNumber,
                                  BatchType = reses.Key.BatchType,
                                  FailedReason = reses.Key.FailedReason,
                                  PeriodId = reses.Key.PeriodId,
                                  AlertType = reses.Key.AlertType,
                                  CustomerNum = reses.Key.CustomerNum,
                                  CustomerName = reses.Key.CustomerName,
                                  BillGroupCode = reses.Key.BillGroupCode,
                                  BillGroupName = reses.Key.BillGroupName,
                                  Class = reses.Key.Class,
                                  Risk = reses.Key.Risk,
                                  CreditLimit = reses.Sum(age => age.CreditLimit),
                                  TotalAmt = reses.Sum(age => age.TotalAmt),
                                  CurrentAmt = reses.Sum(age => age.CurrentAmt),
                                  FDueOver90Amt = reses.Sum(age => age.FDueOver90Amt),
                                  PastDueAmt = reses.Sum(age => age.PastDueAmt),
                                  Operator = reses.Key.Operator,
                                  IsHoldFlg = reses.Key.IsHoldFlg
                              })
                     // left join contactor (contact in hierachy structure, not flat result of normal joins)
                     //join cont in
                     //    (from cust in custalls
                     //     from con in conalls
                     //     where cust.CustomerNum == con.CustomerNum || cust.BillGroupCode == con.GroupCode
                     //     select new
                     //     {
                     //         Id = con.Id,
                     //         CustomerNum = cust.CustomerNum,
                     //         EmailAddress = con.EmailAddress,
                     //         Deal = cust.Deal,
                     //         ToCc = con.ToCc,
                     //         Name = con.Name
                     //     })//CommonRep.GetDbSet<Contactor>() 
                     //   on new { final.CustomerNum, final.Deal } equals new { cont.CustomerNum, cont.Deal }
                     //   into conts
                     join aging in CommonRep.GetDbSet<CustomerAging>().Where(o => o.Deal == AppContext.Current.User.Deal && o.RemoveFlg == "0") on new { final.CustomerNum, final.Deal } equals new { aging.CustomerNum, aging.Deal }
                        into agings
                     where agings.Any(age => age.LegalEntity == legalEntity || string.IsNullOrEmpty(legalEntity))
                     select new SoaDto
                     {
                         Id = final.Id,
                         ActionDate = final.ActionDate,
                         Deal = final.Deal,
                         TaskId = final.TaskId,
                         ReferenceNo = final.ReferenceNo,
                         ProcessId = final.ProcessId,
                         SoaStatus = final.SoaStatus,
                         CauseObjectNumber = final.CauseObjectNumber,
                         BatchType = final.BatchType,
                         FailedReason = final.FailedReason,
                         PeriodId = final.PeriodId,
                         AlertType = final.AlertType,
                         CustomerNum = final.CustomerNum,
                         CustomerName = final.CustomerName,
                         BillGroupCode = string.IsNullOrEmpty(final.BillGroupCode) == true ? final.CustomerName : final.BillGroupCode,
                         BillGroupName = string.IsNullOrEmpty(final.BillGroupName) == true ? final.CustomerName : final.BillGroupName,
                         CreditLimit = final.CreditLimit,
                         TotalAmt = final.TotalAmt,
                         CurrentAmt = final.CurrentAmt,
                         FDueOver90Amt = final.FDueOver90Amt,
                         PastDueAmt = final.PastDueAmt,
                         //ExistCont = conts.Any() ? "Y" : "N",
                         //ContactList = from c in conts
                                       //select string.Concat(c.EmailAddress, "(", c.Name, ")"),
                         LegalEntityList = from l in agings
                                           select l.LegalEntity,
                         Class = final.Class,
                         Risk = final.Risk,
                         Operator = final.Operator,
                         IsHoldFlg = final.IsHoldFlg

                     });
                #endregion
            }
            //var rr = from soa in r 
            //         // left join contactor (contact in hierachy structure, not flat result of normal joins)
            //         join cont in CommonRep.GetDbSet<Contactor>() on new { soa.CustomerNum, soa.Deal } equals new { cont.CustomerNum, cont.Deal }
            //            into conts
            //         join aging in CommonRep.GetDbSet<CustomerAging>() on new { soa.CustomerNum, soa.Deal } equals new { aging.CustomerNum, aging.Deal }
            //            into agings
            //        select new SoaDto
            //         {
            //             Id = soa.Id,
            //             ActionDate = soa.ActionDate,
            //             Deal = soa.Deal,
            //             TaskId = soa.TaskId,
            //             ReferenceNo = soa.ReferenceNo,
            //             ProcessId = soa.ProcessId,
            //             SoaStatus = soa.SoaStatus,
            //             CauseObjectNumber = soa.CauseObjectNumber,
            //             BatchType = soa.BatchType,
            //             FailedReason = soa.FailedReason,
            //             PeriodId = soa.PeriodId,
            //             AlertType = soa.AlertType,
            //             CustomerNum = soa.CustomerNum,
            //             CustomerName = soa.CustomerName,
            //             BillGroupCode = string.IsNullOrEmpty(soa.BillGroupCode) == true ? soa.CustomerName : soa.BillGroupCode,
            //             BillGroupName = string.IsNullOrEmpty(soa.BillGroupName) == true ? soa.CustomerName : soa.BillGroupName,
            //             CreditLimit = soa.CreditLimit,
            //             TotalAmt = soa.TotalAmt,
            //             CurrentAmt = soa.CurrentAmt,
            //             FDueOver90Amt = soa.FDueOver90Amt,
            //             PastDueAmt = soa.PastDueAmt,
            //             ExistCont = conts.Any() ? "Y" : "N",
            //             ContactList = from c in conts
            //                           select string.Concat(c.EmailAddress, "(", c.Name, ")"),
            //             LegalEntityList = from l in agings
            //                               select l.LegalEntity,
            //             Class = soa.Class,
            //             Risk = soa.Risk,
            //             Operator = soa.Operator,
            //             IsHoldFlg = soa.IsHoldFlg

            //         };
            #region bakup
////            var GetAlertSql = string.Format(@"
////                    SELECT * FROM V_COLLECTORSOA WHERE ACTIONDATE<= '{0}' AND DEAL = '{1}' AND OPERATOR = '{2}'
////                ", AppContext.Current.User.Now
////                 , AppContext.Current.User.Deal.ToString()
////                 , AppContext.Current.User.EID.ToString());
//            //return CommonRep.GetDBContext().Database.SqlQuery<CollectorSoaView>(GetAlertSql).AsQueryable();
//            // .SqlQuery(GetAlertSql);

//            int latestPId = CommonRep.GetDbSet<PeriodControl>()
//                .Where(o => o.Deal == AppContext.Current.User.Deal).Max(o => o.Id);

            //var rrr = CommonRep.GetDbSet<CollectorSoaView>().Where(
            //        cs => cs.ActionDate < AppContext.Current.User.Now && cs.Deal == AppContext.Current.User.Deal.ToString()
            //            && cs.Operator == AppContext.Current.User.EID.ToString() && cs.SoaStatus != "Finish"
            //            && cs.PeriodId == latestPId
            //    ).ToList();

            return r;
//            return res.AsQueryable();
            #endregion
        }

        public IEnumerable<SoaDto> GetNoPaging(string ListType)
        {
            //int latestPId = CommonRep.GetDbSet<PeriodControl>()
            //    .Where(o => o.Deal == AppContext.Current.User.Deal).Max(o => o.Id);

            //if (ListType == "batch")
            //{
            //    var res = CommonRep.GetDbSet<CollectorSoaView>().Where(
            //                o => o.ActionDate < AppContext.Current.User.Now 
            //                    && o.Deal == AppContext.Current.User.Deal.ToString()
            //                    && o.Operator == AppContext.Current.User.EID.ToString()
            //                    && o.PeriodId == CurrentPeriod
            //                    && o.BatchType == 1 && o.SoaStatus == "Initialized"
            //            ).OrderBy(o => o.ExistCont).ThenBy(o => o.Class).ThenByDescending(o => o.Risk).ThenBy(o => o.BillGroupName);
            //    return res.AsQueryable();
            //}
            //    //finish
            //else 
            //{
            //    var res = CommonRep.GetDbSet<CollectorSoaView>().Where(
            //    o => o.Deal == AppContext.Current.User.Deal.ToString()
            //        && o.PeriodId == CurrentPeriod
            //        && o.Operator == AppContext.Current.User.EID.ToString()
            //        && o.SoaStatus == "Finish"
            //).OrderBy(o=>o.ProcessId).ThenBy(o => o.ExistCont).ThenBy(o => o.Class).ThenByDescending(o => o.Risk).ThenBy(o => o.BillGroupName);
            //    return res.AsQueryable();
            //}

            IQueryable<SoaDto> r = null;
            //var custalls = CommonRep.GetQueryable<CustomerTeam>().Where(o => o.Deal == AppContext.Current.User.Deal && o.InScope == "Y");
            //var conalls = CommonRep.GetQueryable<Contactor>().Where(o => o.Deal == AppContext.Current.User.Deal && o.IsDefaultFlg == "1");

            if (ListType == "batch")
            {
                #region Without InvoiceState and InvoiceTrackState
                // base table: dunning
                r = (from final in
                         (from res in
                              (from soa in CommonRep.GetDbSet<CollectorAlert>()
                               // left join customer level
                               join cust in SpringFactory.GetObjectImpl<CustomerService>("CustomerService").GetCustomerLevel(CurrentPeriod) on new { soa.CustomerNum, soa.Deal } equals new { cust.CustomerNum, cust.Deal }
                               //   into custs
                               //from cust in custs.DefaultIfEmpty()
                               where soa.Status == "Initialized" && soa.BatchType == 1 && (soa.AlertType == 1) && soa.ActionDate < AppContext.Current.User.Now && soa.PeriodId == CurrentPeriod
                               // left join aging
                               join age in CommonRep.GetQueryable<CustomerAging>().Where(o => o.Deal == AppContext.Current.User.Deal && o.RemoveFlg == "0") on new { cust.CustomerNum, cust.Deal } equals new { age.CustomerNum, age.Deal }
                               //   into ages
                               //from age in ages.DefaultIfEmpty()
                               // left join hold customer table
                               join ch in CommonRep.GetDbSet<HoldCustomer>() on cust.CustomerNum equals ch.CustomerNum
                                  into custHolds
                               select new
                               {
                                   Id = soa.Id,
                                   ActionDate = soa.ActionDate,
                                   Deal = cust.Deal,
                                   TaskId = soa.TaskId,
                                   ReferenceNo = soa.ReferenceNo,
                                   ProcessId = soa.ProcessId,
                                   SoaStatus = soa.Status,
                                   CauseObjectNumber = soa.CauseObjectNumber,
                                   BatchType = soa.BatchType,
                                   FailedReason = soa.FailedReason,
                                   PeriodId = soa.PeriodId,
                                   AlertType = soa.AlertType,
                                   CustomerNum = cust.CustomerNum,
                                   CustomerName = cust.CustomerName,
                                   BillGroupCode = cust.BillGroupCode,
                                   BillGroupName = cust.BillGroupName,
                                   // This is hold flag
                                   IsHoldFlg = custHolds.Any(ch => ch.IsHoldFlg == "1") ? "On Hold" : "Normal",
                                   // TODO: THis is used to shown hold flag type name in UI, We will handle this in UI.
                                   Operator = cust.Collector,
                                   // All contacts list, Another property "Contact" have logic to retrieve single line contact information.
                                   CreditLimit = age == null ? 0 : age.CreditLimit,
                                   TotalAmt = age == null ? 0 : age.TotalAmt,
                                   CurrentAmt = age == null ? 0 : age.CurrentAmt,
                                   FDueOver90Amt = age == null ? 0 : (age.Due120Amt + age.Due150Amt + age.Due180Amt + age.Due210Amt + age.Due240Amt + age.Due270Amt + age.Due300Amt + age.Due330Amt + age.Due360Amt + age.DueOver360Amt),
                                   PastDueAmt = age == null ? 0 : age.DueoverTotalAmt,
                                   Risk = cust.Risk,
                                   Value = cust.Value,
                                   Class = (string.IsNullOrEmpty(cust.ValueLevel) == true ? "LV" : cust.ValueLevel) +
                                           (string.IsNullOrEmpty(cust.RiskLevel) == true ? "LR" : cust.RiskLevel),
                               })
                          group res by new
                          {
                              res.Id,
                              res.ActionDate,
                              res.Deal,
                              res.TaskId,
                              res.ReferenceNo,
                              res.ProcessId,
                              res.SoaStatus,
                              res.CauseObjectNumber,
                              res.BatchType,
                              res.FailedReason,
                              res.PeriodId,
                              res.AlertType,
                              res.CustomerNum,
                              res.CustomerName,
                              res.BillGroupCode,
                              res.BillGroupName,
                              res.IsHoldFlg,
                              res.Operator,
                              res.Risk,
                              res.Value,
                              res.Class
                          }
                              into reses
                              select new
                              {
                                  Id = reses.Key.Id,
                                  ActionDate = reses.Key.ActionDate,
                                  Deal = reses.Key.Deal,
                                  TaskId = reses.Key.TaskId,
                                  ReferenceNo = reses.Key.ReferenceNo,
                                  ProcessId = reses.Key.ProcessId,
                                  SoaStatus = reses.Key.SoaStatus,
                                  CauseObjectNumber = reses.Key.CauseObjectNumber,
                                  BatchType = reses.Key.BatchType,
                                  FailedReason = reses.Key.FailedReason,
                                  PeriodId = reses.Key.PeriodId,
                                  AlertType = reses.Key.AlertType,
                                  CustomerNum = reses.Key.CustomerNum,
                                  CustomerName = reses.Key.CustomerName,
                                  BillGroupCode = reses.Key.BillGroupCode,
                                  BillGroupName = reses.Key.BillGroupName,
                                  Class = reses.Key.Class,
                                  Risk = reses.Key.Risk,
                                  CreditLimit = reses.Sum(age => age.CreditLimit),
                                  TotalAmt = reses.Sum(age => age.TotalAmt),
                                  CurrentAmt = reses.Sum(age => age.CurrentAmt),
                                  FDueOver90Amt = reses.Sum(age => age.FDueOver90Amt),
                                  PastDueAmt = reses.Sum(age => age.PastDueAmt),
                                  Operator = reses.Key.Operator,
                                  IsHoldFlg = reses.Key.IsHoldFlg
                              })
                     // left join contactor (contact in hierachy structure, not flat result of normal joins)
                     //join cont in
                     //    (from cust in custalls
                     //     from con in conalls
                     //     where cust.CustomerNum == con.CustomerNum || cust.BillGroupCode == con.GroupCode
                     //     select new
                     //     {
                     //         Id = con.Id,
                     //         CustomerNum = cust.CustomerNum,
                     //         EmailAddress = con.EmailAddress,
                     //         Deal = cust.Deal,
                     //         ToCc = con.ToCc,
                     //         Name = con.Name
                     //     })//CommonRep.GetDbSet<Contactor>() 
                     //   on new { final.CustomerNum, final.Deal } equals new { cont.CustomerNum, cont.Deal }
                     //join cont in CommonRep.GetDbSet<Contactor>() on new { final.CustomerNum, final.Deal } equals new { cont.CustomerNum, cont.Deal }
                     //   into conts
                     join aging in CommonRep.GetDbSet<CustomerAging>().Where(o => o.Deal == AppContext.Current.User.Deal && o.RemoveFlg == "0") on new { final.CustomerNum, final.Deal } equals new { aging.CustomerNum, aging.Deal }
                        into agings
                     //where agings.Any()
                     select new SoaDto
                     {
                         Id = final.Id,
                         ActionDate = final.ActionDate,
                         Deal = final.Deal,
                         TaskId = final.TaskId,
                         ReferenceNo = final.ReferenceNo,
                         ProcessId = final.ProcessId,
                         SoaStatus = final.SoaStatus,
                         CauseObjectNumber = final.CauseObjectNumber,
                         BatchType = final.BatchType,
                         FailedReason = final.FailedReason,
                         PeriodId = final.PeriodId,
                         AlertType = final.AlertType,
                         CustomerNum = final.CustomerNum,
                         CustomerName = final.CustomerName,
                         BillGroupCode = string.IsNullOrEmpty(final.BillGroupCode) == true ? final.CustomerName : final.BillGroupCode,
                         BillGroupName = string.IsNullOrEmpty(final.BillGroupName) == true ? final.CustomerName : final.BillGroupName,
                         CreditLimit = final.CreditLimit,
                         TotalAmt = final.TotalAmt,
                         CurrentAmt = final.CurrentAmt,
                         FDueOver90Amt = final.FDueOver90Amt,
                         PastDueAmt = final.PastDueAmt,
                         //ExistCont = conts.Any() ? "Y" : "N",
                         //ContactList = from c in conts
                         //              select string.Concat(c.EmailAddress, "(", c.Name, ")"),
                         LegalEntityList = from l in agings
                                           select l.LegalEntity,
                         Class = final.Class,
                         Risk = final.Risk,
                         Operator = final.Operator,
                         IsHoldFlg = final.IsHoldFlg

                     });
                #endregion
            }
            else
            {
                #region Without InvoiceState and InvoiceTrackState
                // base table: 
                r = (from final in
                         (from res in
                              (from soa in CommonRep.GetDbSet<CollectorAlert>()
                               // left join customer level
                               join cust in SpringFactory.GetObjectImpl<CustomerService>("CustomerService").GetCustomerLevel(CurrentPeriod) on new { soa.CustomerNum, soa.Deal } equals new { cust.CustomerNum, cust.Deal }
                               //   into custs
                               //from cust in custs.DefaultIfEmpty()
                               where soa.Status == "Finish" && (soa.AlertType == 1) && soa.PeriodId == CurrentPeriod
                               // left join aging
                               join age in CommonRep.GetQueryable<CustomerAging>().Where(o => o.Deal == AppContext.Current.User.Deal && o.RemoveFlg == "0") on new { cust.CustomerNum, cust.Deal } equals new { age.CustomerNum, age.Deal }
                               //   into ages
                               //from age in ages.DefaultIfEmpty()
                               // left join hold customer table
                               join ch in CommonRep.GetDbSet<HoldCustomer>() on cust.CustomerNum equals ch.CustomerNum
                                  into custHolds
                               select new
                               {
                                   Id = soa.Id,
                                   ActionDate = soa.ActionDate,
                                   Deal = cust.Deal,
                                   TaskId = soa.TaskId,
                                   ReferenceNo = soa.ReferenceNo,
                                   ProcessId = soa.ProcessId,
                                   SoaStatus = soa.Status,
                                   CauseObjectNumber = soa.CauseObjectNumber,
                                   BatchType = soa.BatchType,
                                   FailedReason = soa.FailedReason,
                                   PeriodId = soa.PeriodId,
                                   AlertType = soa.AlertType,
                                   CustomerNum = cust.CustomerNum,
                                   CustomerName = cust.CustomerName,
                                   BillGroupCode = cust.BillGroupCode,
                                   BillGroupName = cust.BillGroupName,
                                   // This is hold flag
                                   IsHoldFlg = custHolds.Any(ch => ch.IsHoldFlg == "1") ? "On Hold" : "Normal",
                                   // TODO: THis is used to shown hold flag type name in UI, We will handle this in UI.
                                   Operator = cust.Collector,
                                   // All contacts list, Another property "Contact" have logic to retrieve single line contact information.
                                   CreditLimit = age == null ? 0 : age.CreditLimit,
                                   TotalAmt = age == null ? 0 : age.TotalAmt,
                                   CurrentAmt = age == null ? 0 : age.CurrentAmt,
                                   FDueOver90Amt = age == null ? 0 : (age.Due120Amt + age.Due150Amt + age.Due180Amt + age.Due210Amt + age.Due240Amt + age.Due270Amt + age.Due300Amt + age.Due330Amt + age.Due360Amt + age.DueOver360Amt),
                                   PastDueAmt = age == null ? 0 : age.DueoverTotalAmt,
                                   Risk = cust.Risk,
                                   Value = cust.Value,
                                   Class = (string.IsNullOrEmpty(cust.ValueLevel) == true ? "LV" : cust.ValueLevel) +
                                           (string.IsNullOrEmpty(cust.RiskLevel) == true ? "LR" : cust.RiskLevel),
                               })
                          group res by new
                          {
                              res.Id,
                              res.ActionDate,
                              res.Deal,
                              res.TaskId,
                              res.ReferenceNo,
                              res.ProcessId,
                              res.SoaStatus,
                              res.CauseObjectNumber,
                              res.BatchType,
                              res.FailedReason,
                              res.PeriodId,
                              res.AlertType,
                              res.CustomerNum,
                              res.CustomerName,
                              res.BillGroupCode,
                              res.BillGroupName,
                              res.IsHoldFlg,
                              res.Operator,
                              res.Risk,
                              res.Value,
                              res.Class
                          }
                              into reses
                              select new
                              {
                                  Id = reses.Key.Id,
                                  ActionDate = reses.Key.ActionDate,
                                  Deal = reses.Key.Deal,
                                  TaskId = reses.Key.TaskId,
                                  ReferenceNo = reses.Key.ReferenceNo,
                                  ProcessId = reses.Key.ProcessId,
                                  SoaStatus = reses.Key.SoaStatus,
                                  CauseObjectNumber = reses.Key.CauseObjectNumber,
                                  BatchType = reses.Key.BatchType,
                                  FailedReason = reses.Key.FailedReason,
                                  PeriodId = reses.Key.PeriodId,
                                  AlertType = reses.Key.AlertType,
                                  CustomerNum = reses.Key.CustomerNum,
                                  CustomerName = reses.Key.CustomerName,
                                  BillGroupCode = reses.Key.BillGroupCode,
                                  BillGroupName = reses.Key.BillGroupName,
                                  Class = reses.Key.Class,
                                  Risk = reses.Key.Risk,
                                  CreditLimit = reses.Sum(age => age.CreditLimit),
                                  TotalAmt = reses.Sum(age => age.TotalAmt),
                                  CurrentAmt = reses.Sum(age => age.CurrentAmt),
                                  FDueOver90Amt = reses.Sum(age => age.FDueOver90Amt),
                                  PastDueAmt = reses.Sum(age => age.PastDueAmt),
                                  Operator = reses.Key.Operator,
                                  IsHoldFlg = reses.Key.IsHoldFlg
                              })
                     // left join contactor (contact in hierachy structure, not flat result of normal joins)
                     //join cont in
                     //    (from cust in custalls
                     //     from con in conalls
                     //     where cust.CustomerNum == con.CustomerNum || cust.BillGroupCode == con.GroupCode
                     //     select new
                     //     {
                     //         Id = con.Id,
                     //         CustomerNum = cust.CustomerNum,
                     //         EmailAddress = con.EmailAddress,
                     //         Deal = cust.Deal,
                     //         ToCc = con.ToCc,
                     //         Name = con.Name
                     //})
                     //CommonRep.GetDbSet<Contactor>() 
                     //on new { final.CustomerNum, final.Deal } equals new { cont.CustomerNum, cont.Deal }
                     //join cont in CommonRep.GetDbSet<Contactor>() on new { final.CustomerNum, final.Deal } equals new { cont.CustomerNum, cont.Deal }
                     //   into conts
                     join aging in CommonRep.GetDbSet<CustomerAging>().Where(o => o.Deal == AppContext.Current.User.Deal && o.RemoveFlg == "0") on new { final.CustomerNum, final.Deal } equals new { aging.CustomerNum, aging.Deal }
                        into agings
                     select new SoaDto
                     {
                         Id = final.Id,
                         ActionDate = final.ActionDate,
                         Deal = final.Deal,
                         TaskId = final.TaskId,
                         ReferenceNo = final.ReferenceNo,
                         ProcessId = final.ProcessId,
                         SoaStatus = final.SoaStatus,
                         CauseObjectNumber = final.CauseObjectNumber,
                         BatchType = final.BatchType,
                         FailedReason = final.FailedReason,
                         PeriodId = final.PeriodId,
                         AlertType = final.AlertType,
                         CustomerNum = final.CustomerNum,
                         CustomerName = final.CustomerName,
                         BillGroupCode = string.IsNullOrEmpty(final.BillGroupCode) == true ? final.CustomerName : final.BillGroupCode,
                         BillGroupName = string.IsNullOrEmpty(final.BillGroupName) == true ? final.CustomerName : final.BillGroupName,
                         CreditLimit = final.CreditLimit,
                         TotalAmt = final.TotalAmt,
                         CurrentAmt = final.CurrentAmt,
                         FDueOver90Amt = final.FDueOver90Amt,
                         PastDueAmt = final.PastDueAmt,
                         //ExistCont = conts.Any() ? "Y" : "N",
                         //ContactList = from c in conts
                         //              select string.Concat(c.EmailAddress, "(", c.Name, ")"),
                         LegalEntityList = from l in agings
                                           select l.LegalEntity,
                         Class = final.Class,
                         Risk = final.Risk,
                         Operator = final.Operator,
                         IsHoldFlg = final.IsHoldFlg

                     });
                #endregion
            }

            return r;
        }

        public IEnumerable<SoaDto> SelectChangePeriod(int PeriodId)
        {
            //if (PeriodId != null)
            //{
            //    var res = CommonRep.GetDbSet<CollectorSoaView>().Where(
            //            o => o.Deal == AppContext.Current.User.Deal.ToString()
            //                && o.PeriodId == PeriodId
            //                && o.Operator == AppContext.Current.User.EID.ToString()
            //                && o.SoaStatus == "Finish"
            //        ).OrderBy(o => o.ProcessId).ThenBy(o => o.ExistCont).ThenBy(o => o.Class).ThenByDescending(o => o.Risk).ThenBy(o => o.BillGroupName);
            //    return res.AsQueryable();
            //}
            //else {
            //    var query = CommonRep.GetDbSet<CollectorSoaView>().Where(
            //            o => o.Deal == AppContext.Current.User.Deal.ToString()
            //                && o.Operator == AppContext.Current.User.EID.ToString()
            //                && o.SoaStatus == "Finish"
            //        ).OrderBy(o => o.ProcessId).ThenBy(o => o.ExistCont).ThenBy(o => o.Class).ThenByDescending(o => o.Risk).ThenBy(o => o.BillGroupName);
            //    return query.AsQueryable();
            //}

            if (PeriodId != null)
            {
                #region Without InvoiceState and InvoiceTrackState
                // base table: 
                var query = (from final in
                                 (from res in
                                      (from soa in CommonRep.GetDbSet<CollectorAlert>()
                                       // left join customer level
                                       join cust in SpringFactory.GetObjectImpl<CustomerService>("CustomerService").GetCustomerLevel(PeriodId) on new { soa.CustomerNum, soa.Deal } equals new { cust.CustomerNum, cust.Deal }
                                          into custs
                                       from cust in custs.DefaultIfEmpty()
                                       where soa.Status == "Finish" && (soa.AlertType == 1) && soa.PeriodId == PeriodId
                                       // left join aging
                                       join age in CommonRep.GetQueryable<CustomerAging>().Where(o => o.Deal == AppContext.Current.User.Deal && o.RemoveFlg == "0") on new { cust.CustomerNum, cust.Deal } equals new { age.CustomerNum, age.Deal }
                                       //   into ages
                                       //from age in ages.DefaultIfEmpty()
                                       // left join hold customer table
                                       join ch in CommonRep.GetDbSet<HoldCustomer>() on cust.CustomerNum equals ch.CustomerNum
                                          into custHolds
                                       select new
                                       {
                                           Id = soa.Id,
                                           ActionDate = soa.ActionDate,
                                           Deal = cust.Deal,
                                           TaskId = soa.TaskId,
                                           ReferenceNo = soa.ReferenceNo,
                                           ProcessId = soa.ProcessId,
                                           SoaStatus = soa.Status,
                                           CauseObjectNumber = soa.CauseObjectNumber,
                                           BatchType = soa.BatchType,
                                           FailedReason = soa.FailedReason,
                                           PeriodId = soa.PeriodId,
                                           AlertType = soa.AlertType,
                                           CustomerNum = cust.CustomerNum,
                                           CustomerName = cust.CustomerName,
                                           BillGroupCode = cust.BillGroupCode,
                                           BillGroupName = cust.BillGroupName,
                                           // This is hold flag
                                           IsHoldFlg = custHolds.Any(ch => ch.IsHoldFlg == "1") ? "On Hold" : "Normal",
                                           // TODO: THis is used to shown hold flag type name in UI, We will handle this in UI.
                                           Operator = cust.Collector,
                                           // All contacts list, Another property "Contact" have logic to retrieve single line contact information.
                                           CreditLimit = age == null ? 0 : age.CreditLimit,
                                           TotalAmt = age == null ? 0 : age.TotalAmt,
                                           CurrentAmt = age == null ? 0 : age.CurrentAmt,
                                           FDueOver90Amt = age == null ? 0 : (age.Due120Amt + age.Due150Amt + age.Due180Amt + age.Due210Amt + age.Due240Amt + age.Due270Amt + age.Due300Amt + age.Due330Amt + age.Due360Amt + age.DueOver360Amt),
                                           PastDueAmt = age == null ? 0 : age.DueoverTotalAmt,
                                           Risk = cust.Risk,
                                           Value = cust.Value,
                                           Class = (string.IsNullOrEmpty(cust.ValueLevel) == true ? "LV" : cust.ValueLevel) +
                                                   (string.IsNullOrEmpty(cust.RiskLevel) == true ? "LR" : cust.RiskLevel),
                                       })
                                  group res by new
                                  {
                                      res.Id,
                                      res.ActionDate,
                                      res.Deal,
                                      res.TaskId,
                                      res.ReferenceNo,
                                      res.ProcessId,
                                      res.SoaStatus,
                                      res.CauseObjectNumber,
                                      res.BatchType,
                                      res.FailedReason,
                                      res.PeriodId,
                                      res.AlertType,
                                      res.CustomerNum,
                                      res.CustomerName,
                                      res.BillGroupCode,
                                      res.BillGroupName,
                                      res.IsHoldFlg,
                                      res.Operator,
                                      res.Risk,
                                      res.Value,
                                      res.Class
                                  }
                                      into reses
                                      select new
                                      {
                                          Id = reses.Key.Id,
                                          ActionDate = reses.Key.ActionDate,
                                          Deal = reses.Key.Deal,
                                          TaskId = reses.Key.TaskId,
                                          ReferenceNo = reses.Key.ReferenceNo,
                                          ProcessId = reses.Key.ProcessId,
                                          SoaStatus = reses.Key.SoaStatus,
                                          CauseObjectNumber = reses.Key.CauseObjectNumber,
                                          BatchType = reses.Key.BatchType,
                                          FailedReason = reses.Key.FailedReason,
                                          PeriodId = reses.Key.PeriodId,
                                          AlertType = reses.Key.AlertType,
                                          CustomerNum = reses.Key.CustomerNum,
                                          CustomerName = reses.Key.CustomerName,
                                          BillGroupCode = reses.Key.BillGroupCode,
                                          BillGroupName = reses.Key.BillGroupName,
                                          Class = reses.Key.Class,
                                          Risk = reses.Key.Risk,
                                          CreditLimit = reses.Sum(age => age.CreditLimit),
                                          TotalAmt = reses.Sum(age => age.TotalAmt),
                                          CurrentAmt = reses.Sum(age => age.CurrentAmt),
                                          FDueOver90Amt = reses.Sum(age => age.FDueOver90Amt),
                                          PastDueAmt = reses.Sum(age => age.PastDueAmt),
                                          Operator = reses.Key.Operator,
                                          IsHoldFlg = reses.Key.IsHoldFlg
                                      })
                             // left join contactor (contact in hierachy structure, not flat result of normal joins)
                             //join cont in
                             //    (from cust in custalls
                             //     from con in conalls
                             //     where cust.CustomerNum == con.CustomerNum || cust.BillGroupCode == con.GroupCode
                             //     select new
                             //     {
                             //         Id = con.Id,
                             //         CustomerNum = cust.CustomerNum,
                             //         EmailAddress = con.EmailAddress,
                             //         Deal = cust.Deal,
                             //         ToCc = con.ToCc,
                             //         Name = con.Name
                             //})
                             //CommonRep.GetDbSet<Contactor>() 
                             //on new { final.CustomerNum, final.Deal } equals new { cont.CustomerNum, cont.Deal }
                             //join cont in CommonRep.GetDbSet<Contactor>() on new { final.CustomerNum, final.Deal } equals new { cont.CustomerNum, cont.Deal }
                             //   into conts
                             join aging in CommonRep.GetDbSet<CustomerAging>().Where(o => o.Deal == AppContext.Current.User.Deal && o.RemoveFlg == "0") on new { final.CustomerNum, final.Deal } equals new { aging.CustomerNum, aging.Deal }
                                into agings
                             select new SoaDto
                             {
                                 Id = final.Id,
                                 ActionDate = final.ActionDate,
                                 Deal = final.Deal,
                                 TaskId = final.TaskId,
                                 ReferenceNo = final.ReferenceNo,
                                 ProcessId = final.ProcessId,
                                 SoaStatus = final.SoaStatus,
                                 CauseObjectNumber = final.CauseObjectNumber,
                                 BatchType = final.BatchType,
                                 FailedReason = final.FailedReason,
                                 PeriodId = final.PeriodId,
                                 AlertType = final.AlertType,
                                 CustomerNum = final.CustomerNum,
                                 CustomerName = final.CustomerName,
                                 BillGroupCode = string.IsNullOrEmpty(final.BillGroupCode) == true ? final.CustomerName : final.BillGroupCode,
                                 BillGroupName = string.IsNullOrEmpty(final.BillGroupName) == true ? final.CustomerName : final.BillGroupName,
                                 CreditLimit = final.CreditLimit,
                                 TotalAmt = final.TotalAmt,
                                 CurrentAmt = final.CurrentAmt,
                                 FDueOver90Amt = final.FDueOver90Amt,
                                 PastDueAmt = final.PastDueAmt,
                                 //ExistCont = conts.Any() ? "Y" : "N",
                                 //ContactList = from c in conts
                                 //              select string.Concat(c.EmailAddress, "(", c.Name, ")"),
                                 LegalEntityList = from l in agings
                                                   select l.LegalEntity,
                                 Class = final.Class,
                                 Risk = final.Risk,
                                 Operator = final.Operator,
                                 IsHoldFlg = final.IsHoldFlg

                             });
                #endregion
                return query;
            }
            else
            {
                return GetNoPaging("complete");
            }
        }

        public DateTime dataConvertToDT(string strData)
        {
            DateTime dt = new DateTime();
            if (!string.IsNullOrEmpty(strData.Trim()))
            {
                return Convert.ToDateTime(strData);
            }

            return dt;
        }

        //get all sendsoa
        public IEnumerable<SendSoaHead> CreateSoa(string ColSoa,string Type)
        {
            //var watch = System.Diagnostics.Stopwatch.StartNew();

            string oper = AppContext.Current.User.Id.ToString();
            string deal = AppContext.Current.User.Deal.ToString();

            IBaseDataService bdSer = SpringFactory.GetObjectImpl<IBaseDataService>("BaseDataService");

            #region createsoalist
            string[] cusGroup = ColSoa.Split(',');
            //cus
            var cusList = CommonRep.GetQueryable<Customer>()
                .Where(o=> o.Deal == deal && cusGroup.Contains(o.CustomerNum)).Include<Customer, CustomerGroupCfg>(c => c.CustomerGroupCfg).ToList();
            Customer cus = new Customer();
            //aging
            var cusAgingList = CommonRep.GetQueryable<CustomerAging>()
                .Where(o => o.Deal == deal && cusGroup.Contains(o.CustomerNum) && o.RemoveFlg == "0").ToList();
            //sendsoa
            List<SendSoaHead> sendsoaList = new List<SendSoaHead>();
            SendSoaHead sendsoa = new SendSoaHead();
            //customerchangehis=>class
            var classList = CommonRep.GetQueryable<CustomerLevelView>()
                .Where(o => o.Deal == deal && cusGroup.Contains(o.CustomerNum)).ToList();
            CustomerLevelView level =  new CustomerLevelView();
            //SpecialNotes
            var SNList = CommonRep.GetQueryable<SpecialNote>().Where(o => o.Deal == deal && cusGroup.Contains(o.CustomerNum)).ToList();
            //Rate
            var rateList = CommonRep.GetQueryable<RateTran>()
                .Where(o => o.Deal == AppContext.Current.User.Deal && o.EffectiveDate <= AppContext.Current.User.Now.Date && o.ExpiredDate >= AppContext.Current.User.Now.Date).ToList();
            //agingDT
            DateTime agingDT = new DateTime();
            PeroidService pservice = SpringFactory.GetObjectImpl<PeroidService>("PeroidService");
            PeriodControl currentP = pservice.getcurrentPeroid();
            agingDT = dataConvertToDT(currentP.PeriodEnd.ToString());
            DateTime agingDT90 = new DateTime();
            agingDT90 = agingDT.AddDays(-90);
            //invoice
            var oldinvoiceList = CommonRep.GetQueryable<InvoiceAging>()
                .Where(o=>o.Deal==deal && cusGroup.Contains(o.CustomerNum)).ToList();
            List<InvoiceAging> newinvoiceList = new List<InvoiceAging>();
            newinvoiceList = oldinvoiceList;

            if (rateList.Count == 0)
            {
                throw new OTCServiceException("There is no foreign currency rate config in system for this period. "
                    + Environment.NewLine + "Please complete it first.");
            }

            foreach (var item in newinvoiceList)
            {
                if (item.Currency != "USD")
                {
                    item.StandardBalanceAmt = (rateList.Find(m => m.ForeignCurrency == item.Currency).Rate == null ? 1 : rateList.Find(m => m.ForeignCurrency == item.Currency).Rate) * item.BalanceAmt;
                }
                else { item.StandardBalanceAmt = item.BalanceAmt; }
            }

            IDunningService service = SpringFactory.GetObjectImpl<IDunningService>("DunningReminderService");
            List<CollectorAlert> reminders = service.GetEstimatedReminders(cusGroup.ToList(), null);

            foreach(var item in cusGroup){
                sendsoa = new SendSoaHead();
                cus = cusList.Find(m => m.Deal == deal && m.CustomerNum == item);
                level = classList.Find(m => m.Deal == deal && m.CustomerNum == item);
                var newCusAgingList = cusAgingList.FindAll(m => m.Deal == deal && m.CustomerNum == item );
                sendsoa.Deal = deal;
                sendsoa.CustomerCode = item;
                sendsoa.CustomerName = cus.CustomerName;
                sendsoa.TotalBalance = newCusAgingList.Sum(m => m.TotalAmt);
                sendsoa.CustomerClass = (string.IsNullOrEmpty(level.ClassLevel) == true ? "LV" : level.ClassLevel)
                    + (string.IsNullOrEmpty(level.RiskLevel) == true ? "LR" : level.RiskLevel);

                //contactHistory
                List<SubContactHistory> ContactHisList = new List<SubContactHistory>();
                SubContactHistory ContactHis = new SubContactHistory();
                var OldConHisList = CommonRep.GetDbSet<ContactHistory>().Where(o => o.Deal == deal && o.CustomerNum == item);
                int ihis = 1;
                foreach (var his in OldConHisList) {
                    ContactHis = new SubContactHistory();
                    ContactHis.SortId = ihis;
                    ContactHis.Deal = his.Deal;
                    ContactHis.CustomerNum = his.CustomerNum;
                    ContactHis.LegalEntity = his.LegalEntity;
                    ContactHis.ContactType = his.ContactType;
                    ContactHis.ContactDate = his.ContactDate;
                    ContactHis.ContactId = his.ContactId;
                    ContactHis.Comments = his.Comments;
                    ContactHisList.Add(ContactHis);
                    ihis++;
                }
                sendsoa.SubContactHistory = ContactHisList;
                //Legal
                List<SoaLegal> sublegalList = new List<SoaLegal>();
                SoaLegal sublegal = new SoaLegal();
                foreach (var legal in newCusAgingList) 
                {
                    var invoice = newinvoiceList
                        .FindAll(m => m.CustomerNum == item && m.LegalEntity == legal.LegalEntity);
                    var inv1 = invoice.FindAll(m => m.States == "004001" || m.States == "004002" || m.States == "004004" || m.States == "004008" || m.States == "004010" || m.States == "004011" || m.States == "004012").OrderBy(m => m.DueDate).ToList();
                    sublegal = new SoaLegal();
                    sublegal.LegalEntity = legal.LegalEntity;
                    sublegal.Country = legal.Country;
                    sublegal.CreditLimit = legal.CreditLimit;
                    sublegal.TotalARBalance = legal.TotalAmt;
                    sublegal.PastDueAmount = legal.DueoverTotalAmt;
                    sublegal.CreditBalance = invoice.FindAll(m => m.BalanceAmt < 0).Sum(m => m.StandardBalanceAmt);
                    sublegal.CurrentBalance = legal.CurrentAmt;
                    sublegal.FCollectableAmount = inv1
                        .FindAll(m => m.DueDate <= agingDT && (m.Class == "DM" || m.Class == "INV")).Sum(m => m.StandardBalanceAmt);
                    sublegal.FOverdue90Amount = inv1
                        .FindAll(m => m.DueDate <= agingDT90 && (m.Class == "DM" || m.Class == "INV")).Sum(m => m.StandardBalanceAmt);
                    var SN = SNList.Find(m => m.CustomerNum == item && m.LegalEntity == legal.LegalEntity);
                    if (SN == null) {
                        sublegal.SpecialNotes = "";
                    } else {
                        sublegal.SpecialNotes = SN.SpecialNotes;
                    }
                    List<SoaInvoice> subinvoiceList = new List<SoaInvoice>();
                    SoaInvoice subinvoice = new SoaInvoice();
                    if (inv1.Count > 0)
                    {
                        foreach (var inv in inv1)
                        {
                            subinvoice = new SoaInvoice();
                            subinvoice.InvoiceId = inv.Id;
                            subinvoice.InvoiceNum = inv.InvoiceNum;
                            subinvoice.CustomerNum = inv.CustomerNum;
                            subinvoice.CustomerName = inv.CustomerName;
                            subinvoice.LegalEntity = inv.LegalEntity;
                            subinvoice.InvoiceDate = inv.InvoiceDate;
                            subinvoice.CreditTerm = inv.CreditTrem;
                            subinvoice.DueDate = inv.DueDate;
                            subinvoice.PurchaseOrder = inv.PoNum;
                            subinvoice.SaleOrder = inv.SoNum;
                            subinvoice.RBO = inv.MstCustomer;
                            subinvoice.InvoiceCurrency = inv.Currency;
                            subinvoice.OriginalInvoiceAmount = inv.OriginalAmt.ToString();
                            subinvoice.OutstandingInvoiceAmount = inv.BalanceAmt;
                            //subinvoice.DaysLate = new TimeSpan(AppContext.Current.User.Now.Ticks).Subtract(new TimeSpan(Convert.ToDateTime(inv.DueDate).Ticks)).Duration().Days.ToString();
                            subinvoice.DaysLate = (AppContext.Current.User.Now.Date - Convert.ToDateTime(inv.DueDate).Date).Days.ToString();
                            subinvoice.InvoiceTrack = !string.IsNullOrEmpty(inv.TrackStates) == false ? "" : Helper.CodeToEnum<TrackStatus>(inv.TrackStates).ToString().Replace("_", " ");
                            subinvoice.Status = !String.IsNullOrEmpty(inv.States) ? Helper.CodeToEnum<InvoiceStatus>(inv.States).ToString().Replace("_", " ") : "";
                            //added by zhangYu 20151205 start
                            subinvoice.PtpDate = inv.PtpDate;
                            //added by zhangYu 20151205 End
                            subinvoice.DocumentType = inv.Class;
                            subinvoice.Comments = inv.Comments;
                            subinvoice.StandardInvoiceAmount = inv.StandardBalanceAmt;
                            subinvoiceList.Add(subinvoice);
                        }
                    }
                    else {
                        subinvoice = new SoaInvoice();
                        subinvoiceList.Add(subinvoice);
                    }
                    // logic to build reminder calendars
                    ReminderCalendar calendar = new ReminderCalendar();
                    // 1. SOA
                    var tracking = calendar.GetTracking(reminders.FindAll(a => a.CustomerNum == item && string.IsNullOrEmpty(a.LegalEntity) ));
                    // 2. Other reminders
                    tracking = calendar.GetTracking(reminders.FindAll(a => a.CustomerNum == item && a.LegalEntity == legal.LegalEntity), tracking);
                    // 3. Append other information shown in UI;
                    bdSer.AppendTrackingConfig(tracking, deal, item, legal.LegalEntity);
                    sublegal.SubTracking = tracking;
                    sublegal.SubInvoice = subinvoiceList;
                    sublegalList.Add(sublegal);
                }
                sendsoa.SubLegal = sublegalList;
                sendsoaList.Add(sendsoa);
            }
            #endregion
            //**********************************WF Start ***********************************
            //IWorkflowService wfservice = SpringFactory.GetObjectImpl<IWorkflowService>("WorkflowService");
            if (IsWF == "true")
            {
                if (Type == "create")
                {
                    if (GetPStatus(ColSoa, "Processing") == 0)
                    {
                    //if (GetTask(ColSoa) == 0)
                    //{
                        Wfchange("4", ColSoa, "start");
                    //}
                    //else
                    //{
                    //    Wfchange("4", ColSoa, "restart");
                    //}
                    }
                }
            }
            return sendsoaList.AsQueryable<SendSoaHead>();
            //}
            //else {
            //    return new List<SendSoaHead>().AsQueryable();
            //}
            
        }

        //review sendsoa :get custNums by taskNo
        public CollectorAlert GetSoa(string TaskNo) 
        {
            string oper = AppContext.Current.User.Id.ToString();
            string deal = AppContext.Current.User.Deal.ToString();
            var alertList = CommonRep.GetQueryable<CollectorAlert>()
                .Where(m => m.ProcessId == TaskNo && m.PeriodId == CurrentPeriod).OrderByDescending(m => m.CreateDate).FirstOrDefault();
            return alertList;
        }

        //get a soa for a status
        public CollectorAlert GetStatus(string ReferenceNo)
        {
            string oper = AppContext.Current.User.Id.ToString();
            string deal = AppContext.Current.User.Deal.ToString();
            var alertList = CommonRep.GetQueryable<CollectorAlert>()
                .Where(m => m.ReferenceNo == ReferenceNo && m.AlertType == 1 && m.PeriodId == CurrentPeriod).OrderByDescending(m => m.CreateDate).FirstOrDefault();
            return alertList;
        }

        //get Invoice Log
        public IEnumerable<InvoiceLog> GetInvLog(string InvNum) 
        {
            var invList =  CommonRep.GetQueryable<InvoiceLog>()
                .Where(o => o.InvoiceId == InvNum && o.Deal == AppContext.Current.User.Deal).Select(o => o).OrderByDescending(m => m.LogDate).ToList();
            List<InvoiceLog> newLogList = new List<InvoiceLog>();
            InvoiceLog log = new InvoiceLog();
            MailTmp mail = new MailTmp();
            Call call = new Call();
            foreach (var inv in invList) {
                log = new InvoiceLog();
                mail = new MailTmp();
                call = new Call();
                string related = "";
                //6:breakPtp 8:HoldCustomer7:changeStatus
                if ((inv.LogType == "1" || inv.LogType == "3" || inv.LogType == "4" || inv.LogType == "5" || inv.LogType == "6" || inv.LogType == "7" || inv.LogType == "8" || inv.LogType == "9") && inv.ProofId != null)
                {
                    //mail = CommonRep.GetDbSet<MailTmp>().Where(o => o.MessageId == inv.ProofId && o.Bussiness_Reference == inv.CustomerNum).FirstOrDefault();
                    //del Bussiness_Reference,Because messageId is key,del bussiness_ref is ok 20160226
                    mail = CommonRep.GetDbSet<MailTmp>().Where(o => o.MessageId == inv.ProofId ).FirstOrDefault();
                    if (mail != null)
                    {
                        related = mail.From + " " + mail.Subject + " " + mail.CreateTime.ToString().Replace("T", " ");
                    }
                    else {
                        related = "";
                    }
                }
                else if (inv.LogType == "2" && inv.ProofId != null)
                {
                    call = CommonRep.GetDbSet<Call>().Where(o => o.ContactId == inv.ProofId).FirstOrDefault();
                    if (call != null)
                    {
                        //related = call.Comments + " " + call.BeginTime.ToString().Replace("T", " ") + call.EndTime.ToString().Replace("T", " ");
                        related = "Detail";
                    }
                    else {
                        related = "";
                    }
                }
                else {
                    related = "";
                }
                log.Id = inv.Id;
                log.Deal = inv.Deal;
                log.CustomerNum = inv.CustomerNum;
                log.InvoiceId = inv.InvoiceId;
                log.LogDate = inv.LogDate;
                log.LogPerson = inv.LogPerson;
                log.LogAction = inv.LogAction;
                log.LogType = inv.LogType;
                log.OldStatus = !string.IsNullOrEmpty(inv.OldTrack) == false ? "" : Helper.CodeToEnum<InvoiceStatus>(inv.OldStatus).ToString().Replace("_"," ");
                log.NewStatus = !string.IsNullOrEmpty(inv.OldTrack) == false ? "" : Helper.CodeToEnum<InvoiceStatus>(inv.NewStatus).ToString().Replace("_", " ");
                log.OldTrack = !string.IsNullOrEmpty(inv.OldTrack) == false ? "" : Helper.CodeToEnum<TrackStatus>(inv.OldTrack).ToString().Replace("_"," ");
                log.NewTrack = !string.IsNullOrEmpty(inv.NewTrack) == false ? "" : Helper.CodeToEnum<TrackStatus>(inv.NewTrack).ToString().Replace("_", " ");
                log.ContactPerson = inv.ContactPerson;
                log.ProofId = inv.ProofId;
                log.Discription = inv.Discription;
                log.RelatedEmail = related;
                newLogList.Add(log);
            }

            return newLogList.AsQueryable();
        }

        //get WF status
        /// <summary>
        /// Whether Task Exist in WF DB 
        /// </summary>
        /// <param name="referenceNo">CustNums</param>
        /// <param name="status">status in task</param>
        /// <returns></returns>
        public int GetPStatus(string referenceNo, string status)
        {
            IWorkflowService wfservice = SpringFactory.GetObjectImpl<IWorkflowService>("WorkflowService");
            string deal = AppContext.Current.User.Deal.ToString();
            string oper = AppContext.Current.User.Id.ToString();
            string eid = AppContext.Current.User.EID.ToString();
            string[] cus = referenceNo.Split(',');
            string DefaultCus = cus[0].ToString();
            var alert = CommonRep.GetQueryable<CollectorAlert>()
                        .Where(o => o.Deal == deal && o.CustomerNum == DefaultCus && o.AlertType == 1 && o.Status != "Cancelled" && o.PeriodId == CurrentPeriod).ToList()
                        .FirstOrDefault();
            if (string.IsNullOrEmpty(alert.CauseObjectNumber))
            {
                return 0;
            }
            else
            {
                if (wfservice.GetProcessStatus("4", alert.CauseObjectNumber, oper, "Processing").Count > 0)
                {
                    return 1;
                }
                else
                {
                    return 0;
                };
            }
        }

        //WFchange
        public void Wfchange(string processDefinationId, string referenceNo, string type)
        {
            IWorkflowService wfservice = SpringFactory.GetObjectImpl<IWorkflowService>("WorkflowService");
            string oper = AppContext.Current.User.Id.ToString();
            string deal = AppContext.Current.User.Deal.ToString();
            string eid = AppContext.Current.User.EID.ToString();
            string[] cus = referenceNo.Split(',');
            string causeObjectNum = Guid.NewGuid().ToString();
            string DefaultCus = cus[0].ToString();
            var alert = new CollectorAlert();
            if (type != "start")
            {
                alert = CommonRep.GetQueryable<CollectorAlert>()
                        .Where(o => o.Deal == deal && o.CustomerNum == DefaultCus && o.AlertType == 1 && o.Status != "Cancelled" && o.PeriodId == CurrentPeriod).ToList()
                        .OrderByDescending(o => o.PeriodId).FirstOrDefault();
            }
            if (type == "start")
            {
                if (IsWF == "true")
                {
                    var task = wfservice.StartProcess(processDefinationId, causeObjectNum, oper);
                    wfservice.AcceptTask(task.TaskId, causeObjectNum, oper);
                    //StartUpdateAlert(referenceNo,task.TaskId);
                    string processid = GetProcessId(task.TaskId);

                    UpdateAlert(cus, task.TaskId.ToString(), processid, causeObjectNum, "Processing", 2);
                }
            }
            else if (type == "restart") {
                //if (IsWF == "true")
                //{
                //    List<string> status = new List<string>();
                //    status.Add("Waiting");
                //    var task = wfservice.GetMyTaskList(oper, status).Find(m => m.CauseObjectNumber == referenceNo);
                //    wfservice.AcceptTask(task.Id, referenceNo, oper);
                //    UpdateAlert(cus, task.Id.ToString(), task.ProcessInstance_Id.ToString(),causeObjectNum, "Processing", 2);
                //}
                //2016-01-12 把Finish的Task重新开启
                UpdateAlert(cus, "", "", "", "Restart", 2);
            }
            else if (type == "cancel")
            {
                if (IsWF == "true")
                {
                    wfservice.CancelTask(processDefinationId, alert.CauseObjectNumber, oper, alert.TaskId);
                    UpdateAlert(cus, "", "","", "Cancel", 2);
                }
            }
            else if (type == "pause")
            {
                if (IsWF == "true")
                {
                    wfservice.PauseProcess(processDefinationId, alert.CauseObjectNumber, oper, alert.TaskId);
                    UpdateAlert(cus, "", "","", "Pause", 2);
                }
            }
            else if (type == "resume")
            {
                if (IsWF == "true")
                {
                    wfservice.ResumeProcess(processDefinationId, alert.CauseObjectNumber, oper, alert.TaskId);
                    UpdateAlert(cus, "", "","", "Resume", 2);
                }
            }
            else if (type == "finish")
            {
                if (IsWF == "true")
                {
                    wfservice.FinishProcess(processDefinationId, alert.CauseObjectNumber, oper, alert.TaskId);
                    UpdateAlert(cus, "", "","", "Finish", 2);
                }
            }
        }

        //Get ProcessId
        public string GetProcessId(long taskid)
        {
            IWorkflowService wfservice = SpringFactory.GetObjectImpl<IWorkflowService>("WorkflowService");
            string oper = AppContext.Current.User.Id.ToString();
            //get processinstanceid
            List<string> status = new List<string>();
            status.Add("Processing");
            var task = wfservice.GetMyTaskList(oper, status).Find(m => m.Id == taskid);
            string processid = task.ProcessInstance_Id.ToString();//wfservice.GetMyTaskList(oper)

            return processid;
        }

        //GetTask
        public int GetTask(string reNo) { 
            IWorkflowService wfservice = SpringFactory.GetObjectImpl<IWorkflowService>("WorkflowService");
            string oper = AppContext.Current.User.Id.ToString();
            List<string> status = new List<string>();
            status.Add("Waiting");
            if (wfservice.GetMyTaskList(oper, status).Find(m => m.CauseObjectNumber == reNo) != null)
            {
                return 1;
            }
            else {
                return 0;
            }
        }
        //save comment
        public void SaveComm(int invid, string comm) {
            InvoiceAging invoice = CommonRep.GetQueryable<InvoiceAging>().Where(m => m.Id == invid).FirstOrDefault();
            invoice.Comments = comm;
            CommonRep.Commit();
        }

        //batch save comment
        public void BatchSaveComm(string invids, string comm)
        {
            string[] strInvidArray = invids.Split(',');
            List<int> intInvidList = Array.ConvertAll<string, int>(strInvidArray, s => int.Parse(s)).ToList<int>();
            var invoiceList = CommonRep.GetQueryable<InvoiceAging>().Where(m => intInvidList.Contains(m.Id));
            foreach (var id in intInvidList)
            {
                InvoiceAging invoice = invoiceList.Where(m => m.Id == id).FirstOrDefault();
                invoice.Comments = comm + Environment.NewLine + invoice.Comments;
            }
            CommonRep.Commit();
        }

        //save notes
        public void SaveNotes(string Cus, string SpNotes)
        {
            Customer cus = CommonRep.GetDbSet<Customer>()
                .Where(m => m.Deal == AppContext.Current.User.Deal && m.CustomerNum == Cus && m.RemoveFlg == "1").FirstOrDefault();
            cus.SpecialNotes = SpNotes;
            CommonRep.Commit();
        }

        //batch send soa
        public void BatchSoa(string Cusnums)
        {
            string deal = AppContext.Current.User.Deal.ToString();
            string eid = AppContext.Current.User.EID.ToString();
            MailService mailservice = SpringFactory.GetObjectImpl<MailService>("MailService");
            ICustomerService custservice = SpringFactory.GetObjectImpl<ICustomerService>("CustomerService");
            ContactService conservice = SpringFactory.GetObjectImpl<ContactService>("ContactService");
            InvoiceService invservice = SpringFactory.GetObjectImpl<InvoiceService>("InvoiceService");
            //1.get batch customer list
            List<string> AllCustNumList = Cusnums.Split(',').ToList();
            //var CustList = CommonRep.GetQueryable<CollectorSoaView>()
            //    .Where(m => m.Deal == deal && m.Operator == eid && m.ExistCont == "Y" && m.BatchType == 1
            //        && m.SoaStatus != "Finish" && m.SoaStatus != "Cancelled" && AllCustNumList.Contains(m.CustomerNum))
            //    .Select(m => m).ToList();
            //List<string> AllCustNumList = CustList.Select(m=>m.CustomerNum).ToList();

            //20160406
            var CustList = GetNoPaging("batch").Where(m => AllCustNumList.Contains(m.CustomerNum)).ToList();

            //Rate
            var rateList = CommonRep.GetQueryable<RateTran>()
                .Where(o => o.Deal == AppContext.Current.User.Deal && o.EffectiveDate <= AppContext.Current.User.Now.Date && o.ExpiredDate >= AppContext.Current.User.Now.Date).ToList();
            //all invoice
            var AllInvList = CommonRep.GetDbSet<InvoiceAging>()
                .Where(o => o.Deal == deal && AllCustNumList.Contains(o.CustomerNum) && (o.States == "004001" || o.States == "004002" || o.States == "004004" || o.States == "004008")).ToList();

            //loop customer list by groupcode
            IEnumerable<IGrouping<string, SoaDto>> query = CustList.GroupBy(m => m.BillGroupCode);
            foreach (IGrouping<string, SoaDto> info in query)
            {
                List<SoaDto> CusGroupList = info.ToList<SoaDto>();

                //get cusnums
                string cusnums = "";
                List<int> invids = new List<int>();
                foreach (var item in CusGroupList)
                {
                    cusnums += item.CustomerNum + ",";
                }
                cusnums = cusnums.Substring(0, cusnums.Length - 1);
                //get invoiceids

                //=========updated by alex body中显示附件名+Currency=== $scope.inv 追加 ======

                //2.get mailinstance
                string subject = "SOA";
                int[] invIds = null;
                //3.get contact into mailinstance.to
                string to = string.Empty;
                //########## update by pxc 增加cc ######################## s
                string cc = string.Empty;
                List<string> contactorsTo = conservice.GetContactsByCustomers(cusnums).Where(m => m.ToCc == "1").Select(m => m.EmailAddress).Distinct().ToList();
                List<string> contactorsCC = conservice.GetContactsByCustomers(cusnums).Where(m => m.ToCc == "2").Select(m => m.EmailAddress).Distinct().ToList();
                if (contactorsTo.Count > 0)
                {
                    foreach (var conto in contactorsTo)
                    {
                        to += conto + ";";
                    }
                    to = to.Substring(0, to.Length - 1);
                }
                if (contactorsCC.Count > 0)
                {
                    foreach (var concc in contactorsCC)
                    {
                        cc += concc + ";";
                    }
                    cc = cc.Substring(0, cc.Length - 1);
                }
                //########## update by pxc 增加cc ######################## e
                //=========================================================================

                //4.get attachment into mailinstance.attachment
                string[] cus = cusnums.Split(',');
                decimal blc = 0;
                foreach (var c in cus) {
                    blc = 0;
                    //=========updated by alex body中显示附件名+Currency=== $scope.inv 追加 ======
                    //2016-01-07 update
                    subject += "-" + CusGroupList.Find(m => m.CustomerNum == c).CustomerNum + "-" + CusGroupList.Find(m => m.CustomerNum == c).CustomerName;
                    
                    AllInvList.FindAll(m => m.CustomerNum == c).ForEach(m =>
                    {
                        invids.Add(m.Id);
                        if (m.Currency != "USD")
                        {
                            blc += Convert.ToDecimal((rateList.Find(n => n.ForeignCurrency == m.Currency).Rate == null ? 1 : rateList.Find(n => n.ForeignCurrency == m.Currency).Rate) * m.BalanceAmt);
                        }
                        else {
                            blc += Convert.ToDecimal(m.BalanceAmt);
                        }
                    });
                    invIds = invids.ToArray();
                    //2016-01-07 delete
                    //instance.Subject += "($" + String.Format("{0:N2}",blc) + ")";
                    //===========================================================================
                }
                //=========updated by alex body中显示附件名+Currency=== $scope.inv 追加 ======
                string attInfo = string.Empty;
                MailTmp instance = GetNewMailInstance(CusGroupList[0].CustomerNum, invids);
                instance.Subject = subject;
                //########## update by pxc 增加MessageId ######################## 
                instance.MessageId = Guid.NewGuid().ToString();
                instance.To = to;
                //########## update by pxc 增加cc ######################## s
                instance.Cc = cc;
                //########## update by pxc 增加cc ######################## e
                instance.invoiceIds = invIds;

                //########## update by pxc 新MailTmp 删除 ######################## 
                //instance.Bussiness_Reference = cusnums;
                //########## update by pxc 增加Mail customer ######################## s
                foreach (var c in cus)
                {
                    CustomerMail cm = new CustomerMail();
                    cm.MessageId = instance.MessageId;
                    cm.CustomerNum = c;
                    instance.CustomerMails.Add(cm); 
                }
                //########## update by pxc 增加Mail customer ######################## e
                instance.MailType = "001,SOA";

                //List<string> soaFiles = invservice.setContent(invids,1,out attInfo);
                //instance.Attachment = string.Join(Mail.ATTACHMENT_SPLITER.ToString(), soaFiles);

                //==========================================================================

                //5.call mailservice SendMail
                //if (IsMail == "true")
                //{
                try
                {
                    //mailservice.SendMail(instance);

                    //6.save mail contacthistory  invoicelog
                    if (sendSoaSaveInfoToDB(instance) == 1)
                    {
                        //alert update 
                        UpdateAlert(cus, "", "", "", "Finish", 1);
                    }
                }
                catch (MailServiceException ex)
                {
                    //alert update 
                    UpdateAlert(cus, ex.Message.ToString(), "", "", "Failed", 1);
                }
                catch (Exception ex)
                {
                    //alert update 
                    UpdateAlert(cus, ex.ToString(), "", "", "Failed", 1);
                }
                //}
                
            }
            
        }

        //update alert
        /// <summary>
        /// update alert when after send soa or pause/resume/cancel/finish workflow
        /// </summary>
        /// <param name="cusnums">array custnums</param>
        /// <param name="TaskId">task id (if workflow )</param>
        /// <param name="ProcessId">processId ( if workflow )</param>
        /// <param name="status">status in alert</param>
        /// <param name="type">batch or execute: 1:batch;2:execute</param>
        public void UpdateAlert(string[] cusnums, string TaskId, string ProcessId,string causeObjectNum,string status,int type) {
            string deal = AppContext.Current.User.Deal.ToString();
            string eid = AppContext.Current.User.EID.ToString();
            //var alertList = CommonRep.GetQueryable<CollectorAlert>()
            //    .Where(o => o.Deal == deal && o.AlertType == 1 && o.Eid == eid).ToList();
            //List<CollectorAlert> alertList = CommonRep.GetQueryable<CollectorAlert>()
            //                                .Where(o => o.Deal == deal && o.AlertType == 1 && o.Status != "Cancelled").ToList();
            // pxc update 20160311
            List<CollectorAlert> alertList = new List<CollectorAlert>();
            //type = 1 : batch
            if (type == 1) {
                if (status == "Finish")
                {
                    List<string> strCondition = new List<string>();
                    var DunBatchType = type.ToString();
                    var DunAlertType = "";

                    foreach (var cus in cusnums)
                    {
                        CollectorAlert alert = new CollectorAlert();
                        // pxc update 20160311
                        alert = CommonRep.GetQueryable<CollectorAlert>().Where(m => m.Deal == deal && m.AlertType == 1 && m.Status != "Cancelled" && m.CustomerNum == cus && m.Status != "Finish" && m.PeriodId == CurrentPeriod).FirstOrDefault();
                        alert.ReferenceNo = string.Join(",", cusnums);
                        alert.Status = status;
                        alert.ActionDate = AppContext.Current.User.Now.Date;
                        strCondition.Add(cus);
                        if (string.IsNullOrEmpty(DunAlertType)) 
                        {
                            DunAlertType = alert.AlertType.ToString();
                        }
                    }
                    CreateDunning(strCondition, DunBatchType, DunAlertType);
                }
                else if (status == "Failed") {
                    foreach (var cus in cusnums)
                    {
                        CollectorAlert alert = new CollectorAlert();
                        // pxc update 20160311
                        alert = CommonRep.GetQueryable<CollectorAlert>().Where(m => m.Deal == deal && m.AlertType == 1 && m.Status != "Cancelled" && m.CustomerNum == cus && m.Status != "Finish" && m.PeriodId == CurrentPeriod).FirstOrDefault();
                        alert.FailedReason = TaskId;
                    }
                }
            }
            //type = 2 : execute
            else if (type == 2) {
                if (status == "Processing")
                {
                    foreach (var cus in cusnums)
                    {
                        CollectorAlert alert = new CollectorAlert();
                        // pxc update 20160311
                        alert = CommonRep.GetQueryable<CollectorAlert>().Where(m => m.Deal == deal && m.AlertType == 1 && m.Status != "Cancelled" && m.CustomerNum == cus && m.Status != "Finish" && m.PeriodId == CurrentPeriod).FirstOrDefault();
                        alert.TaskId = TaskId;
                        alert.ProcessId = ProcessId;
                        alert.ReferenceNo = string.Join(",", cusnums);
                        alert.CauseObjectNumber = causeObjectNum;
                        alert.Status = status;
                    }
                }
                else if (status == "Pause" || status == "Resume" || status == "Finish")
                {
                    List<string> strCondition = new List<string>();
                    var DunTask = "";
                    var DunBatchType = type.ToString();
                    var DunAlertType = "";
                    foreach (var cus in cusnums)
                    {
                        CollectorAlert alert = new CollectorAlert();
                        // pxc update 20160311
                        alert = CommonRep.GetQueryable<CollectorAlert>().Where(m => m.Deal == deal && m.AlertType == 1 && m.Status != "Cancelled" && m.CustomerNum == cus && m.Status != "Finish" && m.PeriodId == CurrentPeriod).FirstOrDefault();
                        alert.Status = status;
                        if (status == "Finish")
                        {
                            alert.ActionDate = AppContext.Current.User.Now.Date;
                        }
                        //Dunning para
                        if (status == "Finish" && string.IsNullOrEmpty(DunTask))
                        {
                            DunTask = alert.TaskId;
                        }
                        if (status == "Finish" && string.IsNullOrEmpty(DunAlertType)) 
                        {
                            DunAlertType = alert.AlertType.ToString();
                        }
                        //2016-01-12  pxc update
                        WorkflowHistoryService wfhser = SpringFactory.GetObjectImpl<WorkflowHistoryService>("WorkflowHistoryService");
                        wfhser.AddOne(alert);
                    }
                    if (status == "Finish")
                    {
                        //Dunning
                        strCondition.Add(DunTask);
                        CreateDunning(strCondition, DunBatchType, DunAlertType);
                    }
                }
                else if (status == "Cancel" || status == "Restart")
                {
                    foreach (var cus in cusnums)
                    {
                        CollectorAlert alert = new CollectorAlert();
                        if (status == "Cancel")
                        {
                            // pxc update 20160311
                            alert = CommonRep.GetQueryable<CollectorAlert>().Where(m => m.Deal == deal && m.AlertType == 1 && m.Status != "Cancelled" && m.CustomerNum == cus && m.Status != "Finish" && m.PeriodId == CurrentPeriod).FirstOrDefault();
                        }
                        else if (status == "Restart") 
                        {
                            // pxc update 20160311
                            alert = CommonRep.GetQueryable<CollectorAlert>().Where(m => m.Deal == deal && m.AlertType == 1 && m.Status != "Cancelled" && m.CustomerNum == cus && m.Status == "Finish" && m.PeriodId == CurrentPeriod).FirstOrDefault();
                        }
                        alert.TaskId = "";
                        alert.ProcessId = "";
                        alert.ReferenceNo = "";
                        alert.CauseObjectNumber = "";
                        alert.Status = "Initialized";
                    }
                }
            }
            CommonRep.Commit();
        }


        ////insert into invoice_log
        //public List<InvoiceLog> AddInvoiceLogList(Mail instance)
        //{
        //    string deal = AppContext.Current.User.Deal.ToString();
        //    string eid = AppContext.Current.User.EID.ToString();
        //    DateTime operDT = AppContext.Current.User.Now;

        //    List<InvoiceAging> invList = (from inv in CommonRep.GetDbSet<InvoiceAging>()
        //                                    where instance.invoiceIds.Contains(inv.Id)
        //                                    select inv).ToList<InvoiceAging>();
            
        //        //.FindAll(m => m.Deal == deal);
        //    List<InvoiceLog> invlogList = new List<InvoiceLog>();
        //    InvoiceLog invlog = new InvoiceLog();
        //    //foreach (var cus in cusnums) {
        //    //    var newinvList = invList.FindAll(m => m.CustomerNum == cus);
        //        foreach (var inv in invList)
        //        {
        //            invlog = new InvoiceLog();
        //            invlog.Deal = deal;
        //            invlog.CustomerNum = inv.CustomerNum;
        //            invlog.InvoiceId = inv.InvoiceNum;
        //            invlog.LogDate = operDT;
        //            invlog.LogPerson = eid;
        //            invlog.LogAction = "SOA";
        //            invlog.LogType = instance.soaFlg;
        //            invlog.OldStatus = inv.States;
        //            invlog.NewStatus = inv.States;
        //            invlog.OldTrack = inv.TrackStates;
        //            invlog.NewTrack = "001";
        //            invlog.ContactPerson = instance.To;
        //            invlog.ProofId = instance.MessageId;
        //            invlog.Discription = "";
        //            invlogList.Add(invlog);
        //        }
        //    //}
        //    //CommonRep.AddRange(invlogList);
        //    //CommonRep.Commit();
        //        //using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 30, 0)))
        //        //{
        //            //(CommonRep.GetDBContext() as OTCEntities).BulkInsert(invlogList);
        //    //    scope.Complete();
        //    //}
        //        return invlogList;
        //}

        // Get all Contactors in one group  (remain to make common method with ContactService)
        //public List<Contactor> GetGroupContactors(string custNum)
        //{
        //    Customer cust = CommonRep.GetQueryable<Customer>().Where(o => o.CustomerNum == custNum && o.Deal == AppContext.Current.User.Deal).FirstOrDefault();
        //    string billGroupCode = cust.BillGroupCode;
        //    List<Contactor> contlist = CommonRep.GetQueryable<Contactor>().Where(o => o.GroupCode == billGroupCode).ToList();
        //    foreach (Contactor cont in contlist)
        //    {
        //        cont.IsGroupLevel = 1;
        //    }
        //    return contlist;
        //}

        //public List<Contactor> GetCustlevelContactors(string custNum)
        //{
        //    return CommonRep.GetQueryable<Contactor>().Where(o => o.CustomerNum == custNum && o.Deal == AppContext.Current.User.Deal).ToList();
        //}

        //public IEnumerable<Contactor> GetSoaContact(string CustNumFCon)
        //{
        //    //string deal = AppContext.Current.User.Deal;
        //    //var cList = CommonRep.GetDbSet<Contactor>()
        //    //    .Where(o => o.Deal == deal && o.CustomerNum == CustNumFCon && o.IsDefaultFlg == "1").Select(o => o);
        //    //return cList.AsQueryable();
        //    List<Contactor> contlist = new List<Contactor>();
        //    contlist = GetCustlevelContactors(CustNumFCon);
        //    //如果能保证数据都有BkcustomerNum可以去掉判断条件
        //    if (CustNumFCon != null) {
        //        List<Contactor> custlvlist = GetGroupContactors(CustNumFCon);
        //        contlist.AddRange(custlvlist);
        //        return contlist;
        //    }
        //    return contlist;
        //}

        public IEnumerable<CustomerPaymentBank> GetSoaPayment(string CustNumFPb)
        {
            string deal = AppContext.Current.User.Deal;
            var pbList = CommonRep.GetQueryable<CustomerPaymentBank>()
                .Where(o => o.Deal == deal && o.CustomerNum == CustNumFPb).Select(o => o);
            pbList.ToList().ForEach( pb=>
            {
                pb.InUse = pb.Flg == "1" ? "Valid" : "Invalid";
            });
            return pbList.AsQueryable();
        }

        public IEnumerable<CustomerPaymentCircle> GetSoaPaymentCircle(string CustNumFPc)
        {
            string deal = AppContext.Current.User.Deal;
            int i = 1;
            //List<SoaPaymentCalender> spcList = new List<SoaPaymentCalender>();
            //SoaPaymentCalender spc = new SoaPaymentCalender();
            var pcList = CommonRep.GetQueryable<CustomerPaymentCircle>()
                .Where(o => o.Deal == deal && o.CustomerNum == CustNumFPc).Select(o => o);
            pcList.ToList().ForEach(pc =>
            {
                //spc = new SoaPaymentCalender();
                //spc.sortId = i++;
                //spc.PaymentDay = pc.PaymentDay;
                //spc.WeekDay = (pc.PaymentDay.DayOfWeek).ToString();
                //spcList.Add(spc);
                pc.sortId = i++;
                pc.weekDay = (pc.PaymentDay.DayOfWeek).ToString();
            });
            return pcList.AsQueryable();
        }
        //add by jiaxing for get contactdomain list
        public IEnumerable<ContactorDomain> GetSoaContactDomain(string CustNumFPd)
        {
            string deal = AppContext.Current.User.Deal;
            int i = 1;
            var pdList = CommonRep.GetQueryable<ContactorDomain>()
                .Where(o => o.Deal == deal && o.CustomerNum == CustNumFPd).Select(o => o);
            pdList.ToList().ForEach(pd =>
               {
                   pd.sortId = i++;
               });
            return pdList.AsQueryable();
        }
        public IEnumerable<ContactHistory> GetContactHistory(string CustNumsFCH)
        {
            string[] cus = CustNumsFCH.Split(',');
            List<ContactHistory> ConHisList = new List<ContactHistory>();
            List<ContactHistory> subConHisList = new List<ContactHistory>();
            foreach (var item in cus) {
                int i = 1;
                subConHisList = new List<ContactHistory>();
                subConHisList = CommonRep.GetDbSet<ContactHistory>().Where(o => o.CustomerNum == item).ToList();
                subConHisList.ForEach(sb =>
                { 
                    sb.sortId = i++; 
                });
                ConHisList.AddRange(subConHisList);
            }
            return ConHisList.AsQueryable();
        }

        //added by zhangYu
        public int sendSoaSaveInfoToDB(MailTmp mailInstance)
        {

            int flag = 0;//0:failed;1:succeed

            //1.SEND MAIL
            MailService mailService = SpringFactory.GetObjectImpl<MailService>("MailService");

            //2. SAVE MAIL_INFO TO T_Mail for each customer
            List<MailTmp> mailList = new List<MailTmp>();
            MailTmp saveMail = new MailTmp();
            //####################### partials:MailTmp return List #######################
            string[] cus = mailInstance.GetRelatedCustomers().ToArray();
            //if (mailInstance.CustomerMails != null)
            //{
            //    cus = mailInstance.CustomerMails.Select(cm => cm.CustomerNum).ToArray();
            //}
            //else
            //{
            //    cus = "0".Split(',');
            //}

            //string strGuid = Guid.NewGuid().ToString();
            //mailInstance.MessageId = strGuid;
            //foreach (string customerNum in cus)
            //{
            //    saveMail = new MailTmp();
            //    //saveMail = mailInstance;
            //    if (customerNum == "0")
            //    {
            //        saveMail.Bussiness_Reference = null;
            //    }
            //    else
            //    {
            //        saveMail.Bussiness_Reference = customerNum;
            //    }
            //saveMail = new MailTmp();
            //    saveMail.Subject = mailInstance.Subject;
            //    saveMail.Body = mailInstance.Body;
            //    saveMail.From = mailInstance.From;
            //    saveMail.To = mailInstance.To;
            //    saveMail.Cc = mailInstance.Cc;
            //    saveMail.Attachment = mailInstance.Attachment;
            //    saveMail.Deal = AppContext.Current.User.Deal;
            //    saveMail.CustomerMails = mailInstance.
            //    saveMail.Type = "OUT";
            //    saveMail.Category = "Sent";
            //    saveMail.Collector = AppContext.Current.User.EID;
            //    saveMail.MailBox = AppContext.Current.User.Email;
            //    saveMail.CreateTime = AppContext.Current.User.Now;
            //    saveMail.Operator = AppContext.Current.User.EID;

                //mailList.Add(mailInstance);
            //}

            List<ContactHistory> ConHisList = new List<ContactHistory>();
            if (cus.Length > 0)
            {
                ContactService cs = SpringFactory.GetObjectImpl<ContactService>("ContactService");
                //get ContactHistoryList
                ConHisList = cs.AddContactHistoryList(cus, mailInstance.To, mailInstance.MessageId, mailInstance.MailType.Split(',')[0], contactHis =>
                {
                    contactHis.ContactType = "Mail";
                });
            }

            //invoice
            var UpdateInvSql = string.Format("");
            List<InvoiceLog> InvLogList = new List<InvoiceLog>();

            // SOA INVOICE_LOG
            InvoiceService invSer = SpringFactory.GetObjectImpl<InvoiceService>("InvoiceService");
            if (mailInstance.soaFlg != "N")
            { 
                InvLogList = invSer.LogInvoice(mailInstance.invoiceIds.ToList(), invLog =>
                {
                    invLog.ContactPerson = mailInstance.To;
                    invLog.ProofId = mailInstance.MessageId;
                    invLog.NewTrack = mailInstance.MailType.Split(',')[0];
                    invLog.LogAction = mailInstance.MailType.Split(',')[1];
                });

                UpdateInvSql = string.Format(@"
                        UPDATE T_INVOICE_AGING SET TRACK_STATES = '{1}' WHERE ID IN ({0});
                    ", string.Join(",", mailInstance.invoiceIds),
                        mailInstance.MailType.Split(',')[0]
                     );
            }
            using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 30, 0)))
            {
                // Send mail logic.
                mailService.SendMail(mailInstance);
                
                mailList.ForEach(ml=>{
                    ml.FileId = mailInstance.FileId;
                });
                // save additional customer related mail.
                //mailService.BulkSaveMail(mailList);

                if (ConHisList != null && ConHisList.Count > 0)
                {
                    //CONTACT_HIS
                    (CommonRep.GetDBContext() as OTCEntities).BulkInsert(ConHisList);
                }

                // SOA INVOICE_LOG
                //save invoice track
                if (mailInstance.soaFlg != "N")
                {
                    CommonRep.GetDBContext().Database.ExecuteSqlCommand(UpdateInvSql);
                    Helper.Log.Info("INVOICE UPDATE COMPLETED.");
                    (CommonRep.GetDBContext() as OTCEntities).BulkInsert(InvLogList);
                    //AddInvoiceLog(mailInstance);
                }
                scope.Complete();
                flag = 1;
            }
            return flag;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="customer"></param>
        /// <param name="language"></param>
        /// <param name="type"></param>
        /// <param name="collectorEID"></param>
        /// <param name="templateId"></param>
        /// <returns></returns>
        public MailTmp GetNewMailInstance(string customerNums, List<int> intIds, int templateId = 0)
        {
            MailTmp res = null;
            string attachment = "";
            
            // 2, retrieve template based on customer information and hint.
            IMailService ms = SpringFactory.GetObjectImpl<IMailService>("MailService");
            MailTemplate tpl = null;
            if (templateId > 0)
            {
                tpl = ms.GetMailTemplateById(templateId);
            }
            else
            {
                tpl = ms.GetMailTemplate(Helper.EnumToCode<MailTemplateType>(MailTemplateType.SOA));
            }

            if (tpl != null)
            {
                res = ms.GetInstanceFromTemplate(tpl, (parser) =>
                {
                    // 1, contactNames used in SOA template
                    ContactService cs = SpringFactory.GetObjectImpl<ContactService>("ContactService");
                    IList<Contactor> contactors = cs.GetContactsByCustomers(customerNums);
                    List<string> cns = new List<string>();
                    string contactNames = string.Empty;
                    foreach (Contactor cont in contactors)
                    {
                        if (cont.ToCc == "1")
                        {
                            if (!cns.Contains(cont.Name))
                            {
                                cns.Add(cont.Name);
                                contactNames += (cont.Name + ", ");
                            }
                        }
                    }
                    contactNames = contactNames.TrimEnd(',');

                    //生成附件并取得附近名和币种的合计值
                    List<SOAInfoReportItem> reportItemList = new List<SOAInfoReportItem>();
                    InvoiceService invServ = SpringFactory.GetObjectImpl<InvoiceService>("InvoiceService");
                    string[] attachPathList = invServ.setContent(intIds, 1, out reportItemList).ToArray();
                    attachment = string.Join(",", attachPathList);

                    string reportStr = invServ.GetInvoiceReport(reportItemList);

                    parser.RegistContext("attachmentInfo", reportStr);

                    //====================================================================

                    parser.RegistContext("contactNames", contactNames);

                    // 2, collector
                    parser.RegistContext("collector", AppContext.Current.User);
                });

                //added by alex body中显示附件名+Currency
                //附件的id
                res.Attachment = attachment;
            }
            else
            {
                throw new OTCServiceException("No matching template was found!", System.Net.HttpStatusCode.NotFound);
            }

            return res;
        }

        //get all period
        public IEnumerable<PeriodControl> GetAllPeriod()
        {
            var periodQuery = CommonRep.GetDbSet<PeriodControl>().Where(o => o.Deal == AppContext.Current.User.Deal);
            foreach (var p in periodQuery) {
                if (p.PeriodBegin <= AppContext.Current.User.Now && AppContext.Current.User.Now <= p.PeriodEnd)
                {
                    p.IsCurrentFlg = "1";
                }
                else {
                    p.IsCurrentFlg = "0";
                }
                p.Period = p.PeriodBegin.ToShortDateString() + " ~ " + p.PeriodEnd.ToShortDateString();
            }
            return periodQuery.OrderByDescending(o => o.Id);
        }

        //Create Dunning
        public void CreateDunning(List<string> strCondition, string DunBatchType,string DunAlertType) 
        {
            IDunningService service = SpringFactory.GetObjectImpl<IDunningService>("DunningReminderService");
            service.insertDunningReminder(strCondition, DunBatchType, DunAlertType);
        }
        
        public int CheckPermission(string ColSoa)
        {
            int Check = 0;
            string CurrentUser = AppContext.Current.User.EID.ToString();
            string[] cusGroup = ColSoa.Split(',');
            List<string> collectors = new List<string>();
            collectors = CommonRep.GetDbSet<CustomerTeam>()
                .Where(o => o.Deal == AppContext.Current.User.Deal && cusGroup.Contains(o.CustomerNum))
                .Select(o => o.Collector).ToList();
            foreach (var item in collectors)
            {
                if (item != CurrentUser)
                {
                    Check = 1;
                }
            }
            return Check;
        }

    }
}
